const connect = require("../sql")

/**
 * @Description:  # TODO 获取所有的学生信息
 * @Author: 文渊
 * @Date: 2023-09-04 15:08:25
 */
async function queryall(req,res){
    
    const {pageno,pagenum} = req.query;

    const params = (+pageno - 1) * pagenum;

    const sql1 = `select * from student,clazz where student.clazzid = clazz.cid limit ${params},${pagenum}`
    const sql2 = "select count(*) from student,clazz where student.clazzid = clazz.cid"
    const result1 = await connect(sql1,[])//查询到的数据
    const result2 = await connect(sql2,[])//查询到的总条数
    if(result1.length){
        //将数据和总条数都要返回给前端
        res.send({code:200,msg:result1,count:result2[0]['count(*)']})
    }else{
        res.send({code:501,msg:"获取所有的学生信息失败"})
    }
}


/**
 * @Description:  # TODO 删除学生信息
 * @Author: 文渊
 * @Date: 2023-09-05 09:31:55
 */
async function remove(req,res){
    const {id} = req.body;
    const sql = "delete from student where sid = ?"
    const result = await connect(sql,[id])
    if(result.affectedRows){
        res.send({code:200,msg:"删除学生信息成功"})
    }else{
        res.send({code:502,msg:"删除学生信息失败"})
    }
}


/**
 * @Description:  # TODO 添加学生信息
 * @Author: 文渊
 * @Date: 2023-09-05 10:03:00
 */
async function add(req,res){
    const {account,password,url,sname,gender,age,email,clazzid} = req.body;
    const sql = "insert into student values(null,?,?,?,?,?,?,?,?)"
    const result = await connect(sql,[account,password,url,sname,gender,age,email,clazzid])
    if(result.affectedRows){
        res.send({code:200,msg:"添加学生信息成功"})
    }else{
        res.send({code:503,msg:"添加学生信息失败"})
    }
}

/**
 * @Description:  # TODO 根据学生ID获取学生信息
 * @Author: 文渊
 * @Date: 2023-09-05 11:11:30
 */
async function queryone(req,res){
    const {id} = req.query;
    const sql = "select * from student where sid = ?"
    const result = await connect(sql,[id])
    if(result.length){
        res.send({code:200,msg:result})
    }else{
        res.send({code:504,msg:"获取学生信息失败"})
    }
}

/**
 * @Description:  # TODO 修改学生信息
 * @Author: 文渊
 * @Date: 2023-09-05 11:13:18
 */
async function edit(req,res){
    const {id,account,password,url,sname,gender,age,email,clazzid} = req.body;
    const sql = "update student set account = ? , password = ? , url = ?, sname = ?,gender = ?,age = ?,email = ?,clazzid = ? where sid = ?"
    const result = await connect(sql,[account,password,url,sname,gender,age,email,clazzid,id])
    if(result.affectedRows){
        res.send({code:200,msg:"修改学生信息成功!"})
    }else{
        res.send({code:505,msg:"修改学生信息失败"})
    }
}

/**
 * @Description:  # TODO 模糊查询学生姓名
 * @Author: 文渊
 * @Date: 2023-09-05 15:33:59
 */
async function like(req,res){
    const {sname} = req.query;
    const sql = "select * from student,clazz where student.clazzid = clazz.cid and student.sname like '%"+sname+"%'"
    console.log("22222222222222");
    console.log(sql);
    const result = await connect(sql,[])
    if(result.length){
        res.send({code:200,msg:result})
    }else{
        res.send({code:506,msg:"获取学生信息失败"})
    }
}

/**
 * @Description:  # TODO 根据班级id获取班级的学生信息
 * @Author: 文渊
 * @Date: 2023-09-05 16:18:32
 */
async function querystudent(req,res){
    const {clazzid} = req.query;
    const sql = "select * from student,clazz where student.clazzid = clazz.cid and student.clazzid = ?"
    const result = await connect(sql,[clazzid])
    if(result.length){
        res.send({code:200,msg:result})
    }else{
        res.send({code:50,msg:"根据班级ID获取班级学生信息失败"})
    }
}
module.exports = {
    queryall,remove,add,queryone,edit,like,querystudent
}